import pymssql
from configs import config_manage

config = config_manage.get_yaml_config()


class SqlServer(object):
    def __init__(self, database_type):
        if database_type == "ZB":
            self.host = config['sqlServer_zb']['host']
            self.port = config['sqlServer_zb']['port']
            self.user = config['sqlServer_zb']['user']
            self.password = config['sqlServer_zb']['pwd']
            self.database = config['sqlServer_zb']['database']
        else:
            self.host = config['sqlServer_md']['host']
            self.port = config['sqlServer_md']['port']
            self.user = config['sqlServer_md']['user']
            self.password = config['sqlServer_md']['pwd']
            self.database = config['sqlServer_md']['database']

    def _GetConnect(self):

        '''得到连接信息，返回conn.cursor()'''
        self.conn = pymssql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                    database=self.database,
                                    charset="utf8")
        cur = self.conn.cursor()  # 定义游标
        if not cur:
            raise NameError('连接数据库失败')
        else:
            return cur

    def ExecQuery(self, sql):
        '''
        查询模块，传入查询语句，返回查询结果
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
        '''
        cur = self._GetConnect()  # 连接数据库
        cur.execute(sql)  # 查询SQL语句
        reslist = cur.fetchall()  # 查询结果
        self.conn.close()  # 关闭连接
        return reslist

    def ExecNotQuery(self, sql):
        '''
        执行非查询语句
        '''
        cur = self._GetConnect()
        cur.execute(sql)
        self.conn.commit()  # update/delete/insert必须要这一步
        self.conn.close()


if __name__ == '__main__':
    sql = "select *from mall_mall where id='B56B37CF-1F38-41D1-9A3D-E2DEE4C55615';"
    a = SqlServer("ZB")
    result = a.ExecQuery(sql)
    print(result)
